package com.powerunion.datacollection.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;

import com.powerunion.datacollection.common.Constants;
import com.powerunion.datacollection.dao.DaoException;
import com.powerunion.datacollection.dao.GlobalTargetsDataDao;
import com.powerunion.datacollection.model.GlobalTargetsData;
import com.powerunion.datacollection.model.TargetsCategory;
import com.powerunion.datacollection.model.jsonObject.GlobalTargetsDataJSONObject;
import com.powerunion.datacollection.persistence.db.jdbc.operator.QueryOperator;
import com.powerunion.datacollection.persistence.db.jdbc.operator.RsDataSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameter;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameterSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlType;
import com.powerunion.datacollection.persistence.db.jdbc.operator.UpdateOperator;
import com.powerunion.datacollection.service.TargetManager;
import com.powerunion.util.DBUtils;
import com.powerunion.util.log.LogHelper;

public class GlobalTargetsDataDaoImpl extends JdbcBase implements GlobalTargetsDataDao{
	// 初始化日志类
	private static final Logger logger = LogHelper.getInstance().getLogger(
			GlobalTargetsDataDaoImpl.class);
		
	public boolean delete(String formId) throws DaoException{
		if (logger.isDebugEnabled()) {
			logger.debug(Constants.INTOMETHOD + "delete");
		}
		String sql = "delete from dc_global_data where document_id = ? and i_use = 0 and i_template = 0";
		UpdateOperator update = new UpdateOperator(); 
		update.setSql(sql);
		
		update.addParameter(new SqlParameter(SqlType.VARCHAR,formId));
		try{
			update.access();
		}catch(Exception e){
			logger.error("删除表单为ID:"+formId+" 指标初始化表单数据出错！", e);
			throw new DaoException("删除指标初始化表单数据出错！");
		}
		
		int effectCount = update.getEffectCounts();
		if(effectCount >0) {
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "指标值信息"
						+ Constants.SUCCESS);
			}
			return true;
		}else{
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "指标值信息"
						+ Constants.FAIL);
			}
			return false;
		}
	}
	
	private boolean delete(String submiUser,String procId,String actId,String formId,Connection conn)
	 throws DaoException {
		String sql = "delete from dc_global_data where submi_user=? and proc_id=? and document_id=? and act_id=? and i_template=1";
		UpdateOperator update = new UpdateOperator();
		update.setSql(sql);
		update.setUseOnlyConnectionFlag(true);
		update.setPresentConnection(conn);
		update.addParameter(new SqlParameter(SqlType.VARCHAR,submiUser));
		update.addParameter(new SqlParameter(SqlType.VARCHAR,procId));
		update.addParameter(new SqlParameter(SqlType.VARCHAR,formId));
		update.addParameter(new SqlParameter(SqlType.VARCHAR,actId));
		try{
			update.access();
		}catch(Exception e){
			logger.error("删除指标报送信息出错！", e);
			throw new DaoException("删除指标报送信息出错！");
		}
		
		int effectCount = update.getEffectCounts();
		if(effectCount >0) {
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "删除指标报送信息"
						+ Constants.SUCCESS);
			}
			return true;
		}else{
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "删除指标报送信息"
						+ Constants.FAIL);
			}
			return false;
		}
		
	}
	
	public boolean save(List globalTargetsDataList, String formId,int permissionUPFile) throws DaoException {
		if (logger.isDebugEnabled()) {
			logger.debug(Constants.INTOMETHOD + "save");
		}
	
		StringBuffer sqlSave = new StringBuffer();
		sqlSave
				.append(
						"insert into dc_global_data (document_id,targets_category_id,target_id,kernel_value,num_ext01,num_ext02,num_ext03,")
				.append(
						"num_ext04,num_ext05,num_ext06,num_ext07,num_ext08,num_ext09,num_ext10,char_ext01,char_ext02,char_ext03,")
				.append(
						"char_ext04,char_ext05,i_template,i_use,i_permission_upfile,id,sub_date) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0,0,?,?,sysdate)");
		
		UpdateOperator update = new UpdateOperator();
		update.setSql( sqlSave.toString());

		for (Iterator iter = globalTargetsDataList.iterator(); iter.hasNext();) {
			GlobalTargetsDataJSONObject gtdj = (GlobalTargetsDataJSONObject) iter
					.next();
			for(Iterator iter2 = gtdj.getDataArray().iterator();iter2.hasNext();){
				GlobalTargetsData gtd = (GlobalTargetsData)iter2.next();
				SqlParameterSet spSet = new SqlParameterSet();
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,formId));
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtdj.getCategoryId()));
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getTargetId()));
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getKernel_value()));
				spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext01()));
				spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext02()));
				spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext03()));
				spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext04()));
				spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext05()));
				spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext06()));
				spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext07()));
				spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext08()));
				spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext09()));
				spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext10()));
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getChar_ext01()));
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getChar_ext02()));
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getChar_ext03()));
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getChar_ext04()));
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getChar_ext05()));
				spSet.addParameter(new SqlParameter(SqlType.INTEGER,new Integer(permissionUPFile)));
				
				String id = String.valueOf(this.getSeq("dc_global_data"));
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,id));
				update.addBatchParameter(spSet);
			}
		}

		try{
			update.access();
		}catch(Exception e){
			logger.error("保存初始化指标报送表单数据出错！", e);
			throw new DaoException("保存初始化指标报送表单数据出错！");
		}
		int[] effectCount = update.getBatchEffectCounts();
		if(effectCount != null && effectCount.length>0) {
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "初始化指标报送表单数据"
						+ Constants.SUCCESS);
			}
			return true;
		}else{
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "初始化指标报送表单数据"
						+ Constants.FAIL);
			}
			return false;
		}
	
	}

	public boolean save(List globalTargetsDataList, String formId,String submiUser,String procId,int iTemplate,String actId)
	 throws DaoException {
		if (logger.isDebugEnabled()) {
			logger.debug(Constants.INTOMETHOD + "save");
		}
	    Connection conn = null;
		
		StringBuffer sqlSave = new StringBuffer();
		sqlSave
				.append(
						"insert into dc_global_data (document_id,targets_category_id,target_id,kernel_value,num_ext01,num_ext02,num_ext03,")
				.append(
						"num_ext04,num_ext05,num_ext06,num_ext07,num_ext08,num_ext09,num_ext10,char_ext01,char_ext02,char_ext03,char_ext04,")
				.append(
						"char_ext05,i_template,submi_user,proc_id,act_id,id,sub_date) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate)");
		
		UpdateOperator update = new UpdateOperator();
		
		try{
			conn = this.getConnection();
		
			//保存新数据之前先删除原有的数据
			this.delete(submiUser, procId, actId, formId, conn);
			
			update.setUseOnlyConnectionFlag(true);
			update.setPresentConnection(conn);
			update.setSql( sqlSave.toString());

			for (Iterator iter = globalTargetsDataList.iterator(); iter.hasNext();) {
				GlobalTargetsDataJSONObject gtdj = (GlobalTargetsDataJSONObject) iter
						.next();
				for(Iterator iter2 = gtdj.getDataArray().iterator();iter2.hasNext();){
					GlobalTargetsData gtd = (GlobalTargetsData)iter2.next();
					SqlParameterSet spSet = new SqlParameterSet();
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,formId));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtdj.getCategoryId()));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getTargetId()));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getKernel_value()));
					spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext01()));
					spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext02()));
					spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext03()));
					spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext04()));
					spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext05()));
					spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext06()));
					spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext07()));
					spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext08()));
					spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext09()));
					spSet.addParameter(new SqlParameter(SqlType.DECIMAL,gtd.getNum_ext10()));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getChar_ext01()));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getChar_ext02()));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getChar_ext03()));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getChar_ext04()));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getChar_ext05()));
					spSet.addParameter(new SqlParameter(SqlType.INTEGER,new Integer(1)));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,submiUser));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,procId));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,actId));
		
					String id = String.valueOf(this.getSeq("dc_global_data"));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,id));
					update.addBatchParameter(spSet);
				}
			}
			update.access();
		}catch(Exception e){
			logger.error("保存指标报送数据出错！", e);
			throw new DaoException("保存指标报送数据出错！");
		}finally{
			DBUtils.close(conn);
		}
		
		int[] effectCount = update.getBatchEffectCounts();
		if(effectCount != null && effectCount.length>0) {
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "指标报送信息"
						+ Constants.SUCCESS);
			}
			return true;
		}else{
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "指标报送信息"
						+ Constants.FAIL);
			}
			return false;
		}
	
	}
	
	private List getTargetCategorys(String formId,Connection conn ) {
		List targetCategorys = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct targets_category_id,c.category_name from dc_global_data g ")
		   .append(" left join DC_item_CATEGORY c on g.targets_category_id = c.id ")
		   .append("where document_id=? and i_template = 0");
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, formId);
			rs = ps.executeQuery();
			while(rs.next()){
				TargetsCategory tc = new TargetsCategory();
				tc.setId(rs.getString("targets_category_id"));
				tc.setCategoryName(rs.getString("category_name"));
				targetCategorys.add(tc);
			}
		}catch(Exception e){
			logger.error("", e);
			return targetCategorys;
		}finally{
			DBUtils.close(rs, ps);
		}
		
		return targetCategorys;	
	}	
	
	private List getTargetCategorys(String formId,Connection conn ,String submiUser,String procId,String actId) {
		List targetCategorys = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct targets_category_id,c.category_name from dc_global_data g ")
		   .append(" left join DC_item_CATEGORY c on g.targets_category_id = c.id ")
		   .append("where i_template = 1 and submi_user=? and proc_id=? and document_id=? and act_id=? ");
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, submiUser);
			ps.setString(2, procId);
			ps.setString(3, formId);
			ps.setString(4, actId);
			
			rs = ps.executeQuery();
			while(rs.next()){
				TargetsCategory tc = new TargetsCategory();
				tc.setId(rs.getString("targets_category_id"));
				tc.setCategoryName(rs.getString("category_name"));
				targetCategorys.add(tc);
			}
		}catch(Exception e){
			logger.error("", e);
			return targetCategorys;
		}finally{
			DBUtils.close(rs, ps);
		}
		return targetCategorys;	
	}	
	private List getTargetCategorys(String formId,Connection conn ,String procId,String actId) {
		List targetCategorys = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct targets_category_id,c.category_name from dc_global_data g ")
		   .append(" left join DC_item_CATEGORY c on g.targets_category_id = c.id ")
		   .append("where i_template = 1  and proc_id=? and document_id=? and act_id=? ");
		PreparedStatement ps = null; 
		ResultSet rs = null;
		try{
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, procId);
			ps.setString(2, formId);
			ps.setString(3, actId);
			
			rs = ps.executeQuery();
			while(rs.next()){
				TargetsCategory tc = new TargetsCategory();
				tc.setId(rs.getString("targets_category_id"));
				tc.setCategoryName(rs.getString("category_name"));
				targetCategorys.add(tc);
			}
		}catch(Exception e){
			logger.error("", e);
			return targetCategorys;
		}finally{
			DBUtils.close(rs, ps);
		}
		return targetCategorys;	
	}	
	public List getTargetSubmiData(String documentId,String submiUser,String procId,String actId) throws DaoException {
		StringBuffer sql = new StringBuffer();
		sql
				.append("select g.target_id,t.target_name,g.kernel_value,g.num_ext01,g.num_ext02,g.num_ext03, ")
				.append("g.num_ext04,g.num_ext05,g.num_ext06,g.num_ext07,g.num_ext08,g.num_ext09,g.num_ext10,")
				.append("g.char_ext01,g.char_ext02,g.char_ext03,g.char_ext04,g.char_ext05,f.user_name,g.document_id ")
				.append(
						"from dc_global_data g left join dc_target t on g.target_id=t.id ")
				.append("left join frameuser f on g.submi_user = f.user_code ")
				.append(
						" where g.document_id=? and g.i_template = 1 and g.targets_category_id=? and submi_user=? and act_id=?");
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List templateData = null;
		try {
			templateData = new ArrayList();
			conn = this.getConnection();
			List targetCategorys = new ArrayList();
			targetCategorys = this.getTargetCategorys(documentId, conn, submiUser, procId, actId);
			TargetManager tm = new TargetManager();
			for (Iterator iter = targetCategorys.iterator(); iter.hasNext();) {
				TargetsCategory tc = (TargetsCategory) iter.next();
				List targetGroup = new ArrayList();
				ps = conn.prepareStatement(sql.toString());
				ps.setString(1, documentId);
				ps.setString(2, tc.getId());
				ps.setString(3, submiUser);
				ps.setString(4, actId);
				rs = ps.executeQuery();
				while (rs.next()) {
					GlobalTargetsData gtd = new GlobalTargetsData();
					
					gtd.setId(rs.getString("target_id"));
					gtd.setText(rs.getString("target_name"));
					gtd.setKernel_value(rs.getString("kernel_value"));
					gtd.setNum_ext01(rs.getBigDecimal("num_ext01"));
					gtd.setNum_ext02(rs.getBigDecimal("num_ext02"));
					gtd.setNum_ext03(rs.getBigDecimal("num_ext03"));
					gtd.setNum_ext04(rs.getBigDecimal("num_ext04"));
					gtd.setNum_ext05(rs.getBigDecimal("num_ext05"));
					gtd.setNum_ext06(rs.getBigDecimal("num_ext06"));
					gtd.setNum_ext07(rs.getBigDecimal("num_ext07"));
					gtd.setNum_ext08(rs.getBigDecimal("num_ext08"));
					gtd.setNum_ext09(rs.getBigDecimal("num_ext09"));
					gtd.setNum_ext10(rs.getBigDecimal("num_ext10"));
					gtd.setChar_ext01(rs.getString("char_ext01"));
					gtd.setChar_ext02(rs.getString("char_ext02"));
					gtd.setChar_ext03(rs.getString("char_ext03"));
					gtd.setChar_ext04(rs.getString("char_ext04"));
					gtd.setChar_ext05(rs.getString("char_ext05"));
					gtd.setSubmiUser(rs.getString("user_name"));
					gtd.setDocumentId(rs.getString("document_id"));
					
					List targetDetails = tm.findTargetDetails(gtd.getId());
					gtd.setTargetDetails(targetDetails);

					targetGroup.add(gtd);
				}
				tc.setTargets(targetGroup);
				templateData.add(tc);
				DBUtils.close(rs, ps);
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("取得公文id为：" + documentId + " 的指标报送模板初始化数据出错！");
		} finally {
			DBUtils.close(rs, ps, conn);
		}

		return templateData;
	}

	public List getTemplateData(String documentId) throws DaoException {
		StringBuffer sql = new StringBuffer();
		sql
				.append("select g.target_id,t.target_name,g.kernel_value,g.num_ext01,g.num_ext02,g.num_ext03, ")
				.append("g.num_ext04,g.num_ext05,g.num_ext06,g.num_ext07,g.num_ext08,g.num_ext09,g.num_ext10,")
				.append("g.char_ext01,g.char_ext02,g.char_ext03,g.char_ext04,g.char_ext05 ")
				.append(
						"from dc_global_data g left join dc_target t on g.target_id=t.id ")
				.append(
						" where g.document_id=? and g.i_template = 0 and g.targets_category_id=?");

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List templateData = null;
		try {
			DecimalFormat format = new DecimalFormat("0.00");
			templateData = new ArrayList();
			conn = this.getConnection();
			List targetCategorys = new ArrayList();
			targetCategorys = this.getTargetCategorys(documentId, conn);
			TargetManager tm = new TargetManager();
			for (Iterator iter = targetCategorys.iterator(); iter.hasNext();) {
				TargetsCategory tc = (TargetsCategory) iter.next();
				List targetGroup = new ArrayList();
				ps = conn.prepareStatement(sql.toString());
				ps.setString(1, documentId);
				ps.setString(2, tc.getId());
				rs = ps.executeQuery();
				while (rs.next()) {
					GlobalTargetsData gtd = new GlobalTargetsData();
					gtd.setId(rs.getString("target_id"));
					gtd.setText(rs.getString("target_name"));
					gtd.setKernel_value(rs.getString("kernel_value"));
					
					gtd.setNum_ext01(rs.getBigDecimal("num_ext01"));
					gtd.setNum_ext02(rs.getBigDecimal("num_ext02"));
					gtd.setNum_ext03(rs.getBigDecimal("num_ext03"));
					gtd.setNum_ext04(rs.getBigDecimal("num_ext04"));
					gtd.setNum_ext05(rs.getBigDecimal("num_ext05"));
					gtd.setNum_ext06(rs.getBigDecimal("num_ext06"));
					gtd.setNum_ext07(rs.getBigDecimal("num_ext07"));
					gtd.setNum_ext08(rs.getBigDecimal("num_ext08"));
					gtd.setNum_ext09(rs.getBigDecimal("num_ext09"));
					gtd.setNum_ext10(rs.getBigDecimal("num_ext10"));
					gtd.setChar_ext01(rs.getString("char_ext01"));
					gtd.setChar_ext02(rs.getString("char_ext02"));
					gtd.setChar_ext03(rs.getString("char_ext03"));
					gtd.setChar_ext04(rs.getString("char_ext04"));
					gtd.setChar_ext05(rs.getString("char_ext05"));
					
					List targetDetails = tm.findTargetDetails(gtd.getId());
					gtd.setTargetDetails(targetDetails);

					targetGroup.add(gtd);
				}
				tc.setTargets(targetGroup);
				templateData.add(tc);
				DBUtils.close(rs, ps);
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("取得公文id为：" + documentId + " 的指标报送模板初始化数据出错！");
		} finally {
			DBUtils.close(rs, ps, conn);
		}

		return templateData;
	}
	
	public boolean updateFormState(String formId) throws DaoException{
		UpdateOperator update = new UpdateOperator();
		String sql = "update dc_global_data set i_use=1 where document_id = ?";
		update.setSql(sql);
		update.addParameter(new SqlParameter(SqlType.VARCHAR,formId));
		
		try{
			update.access();
		}catch(Exception e){
			logger.error("更新初始化指标报送表单状态出错！", e);
			throw new DaoException("更新初始化指标报送表单状态出错！");
		}
		int effectCount = update.getEffectCounts();
		if(effectCount >0) {
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "更新初始化指标报送表单状态"
						+ Constants.SUCCESS);
			}
			return true;
		}else{
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "更新初始化指标报送表单状态"
						+ Constants.FAIL);
			}
			return false;
		}
	}
	/**
	 * 审批步骤查看提报上来的数据
	 * @param documentId
	 * @param submiUser
	 * @param procId
	 * @param actId
	 * @return
	 * @throws DaoException
	 */
	public List getApproveActivityData(String documentId,String procId,String actId) throws DaoException {
		StringBuffer sql = new StringBuffer();
		sql
				.append("select g.target_id,t.target_name,g.kernel_value,g.num_ext01,g.num_ext02,g.num_ext03, ")
				.append("g.num_ext04,g.num_ext05,g.num_ext06,g.num_ext07,g.num_ext08,g.num_ext09,g.num_ext10,")
				.append("g.char_ext01,g.char_ext02,g.char_ext03,g.char_ext04,g.char_ext05,f.user_name,g.document_id ")
				.append(
						"from dc_global_data g left join dc_target t on g.target_id=t.id ")
				.append("left join frameuser f on g.submi_user = f.user_code ")
				.append(
						" where g.document_id=? and g.i_template = 1 and g.targets_category_id=? ");
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List templateData = null;
		try {
			templateData = new ArrayList();
			conn = this.getConnection();
			List targetCategorys = new ArrayList();
			targetCategorys = this.getTargetCategorys(documentId, conn, procId, actId);
			TargetManager tm = new TargetManager();
			for (Iterator iter = targetCategorys.iterator(); iter.hasNext();) {
				TargetsCategory tc = (TargetsCategory) iter.next();
				List targetGroup = new ArrayList();
				ps = conn.prepareStatement(sql.toString());
				ps.setString(1, documentId);
				ps.setString(2, tc.getId());
				rs = ps.executeQuery();
				while (rs.next()) {
					GlobalTargetsData gtd = new GlobalTargetsData();
					
					gtd.setId(rs.getString("target_id"));
					gtd.setText(rs.getString("target_name"));
					gtd.setKernel_value(rs.getString("kernel_value"));
					gtd.setNum_ext01(rs.getBigDecimal("num_ext01"));
					gtd.setNum_ext02(rs.getBigDecimal("num_ext02"));
					gtd.setNum_ext03(rs.getBigDecimal("num_ext03"));
					gtd.setNum_ext04(rs.getBigDecimal("num_ext04"));
					gtd.setNum_ext05(rs.getBigDecimal("num_ext05"));
					gtd.setNum_ext06(rs.getBigDecimal("num_ext06"));
					gtd.setNum_ext07(rs.getBigDecimal("num_ext07"));
					gtd.setNum_ext08(rs.getBigDecimal("num_ext08"));
					gtd.setNum_ext09(rs.getBigDecimal("num_ext09"));
					gtd.setNum_ext10(rs.getBigDecimal("num_ext10"));
					gtd.setChar_ext01(rs.getString("char_ext01"));
					gtd.setChar_ext02(rs.getString("char_ext02"));
					gtd.setChar_ext03(rs.getString("char_ext03"));
					gtd.setChar_ext04(rs.getString("char_ext04"));
					gtd.setChar_ext05(rs.getString("char_ext05"));
					gtd.setSubmiUser(rs.getString("user_name"));
					gtd.setDocumentId(rs.getString("document_id"));

					List targetDetails = tm.findTargetDetails(gtd.getId());
					gtd.setTargetDetails(targetDetails);

					targetGroup.add(gtd);
				}
				tc.setTargets(targetGroup);
				templateData.add(tc);
				DBUtils.close(rs, ps);
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("getApproveActivityData throw error！");
		} finally {
			DBUtils.close(rs, ps, conn);
		}

		return templateData;
	}
	
	public int getPermissionUPFile(String formId) throws DaoException {
		StringBuffer sql = new StringBuffer();
		sql.append("select i_permission_upfile from dc_global_data where document_id = ? and i_template = 0 ");
		
		QueryOperator query = new QueryOperator();
		query.setSql(sql.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR,formId));
		int  permissionUPFile = 0;
		try{
			query.access();
			if(query.resultSetAvailable()){
				//获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());
				
				if(rs.rowCount>0){
					permissionUPFile = rs.getFieldValueAsInteger("i_permission_upfile").intValue();
				}

				rs.clearAll();
			}
			
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("查找编码为："+formId+"的表单是否允许上传附件！");
		}
		return permissionUPFile;
	}
	
	
	

}
